﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wicresoft.UnifyShow.Business;
using Oracle.DataAccess.Client;
using System.Data;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    public class Change : Basic, IChange
    {
        public decimal GetChangeCountBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*)  from change where status > 0";
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            //OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<ChangeModel>.GetModelCount_By_DirectSql(sqlStr, cmdParms, oda);
        }

        public decimal GetclosedChangeCountBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*)  from change where ( status=11 Or status = 10)";
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            //OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<ChangeModel>.GetModelCount_By_DirectSql(sqlStr, cmdParms, oda);
        }
        public List<IncidentAndChangeModel> GetChangeCountGroupTimeBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) alls,to_char(TicketCreateTime, 'yyyyMM') sign from change where status > 0";
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            sqlStr += " group by  to_char(TicketCreateTime, 'yyyyMM')";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentAndChangeModel>.GetModel_By_OracleDataReader(dr);
        }

        public decimal GetChangeResolvedTimeOutCountBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) from change where (status =11 Or status = 10) and endtime > planendtime";
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            //OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<ChangeModel>.GetModelCount_By_DirectSql(sqlStr, cmdParms, oda);
        }


        public List<IncidentAndChangeModel> GetChangeGroupResultBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) alls,CloseCode ID from change where (status = 11 or status = 10)";
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            sqlStr += " group by CloseCode";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentAndChangeModel>.GetModel_By_OracleDataReader(dr);
        }

        public List<ChangeModel> GetSuccessChangeBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select * from change where (status = 11 or status = 10) and (closecode = 0 OR closecode = 1)";
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<ChangeModel>.GetModel_By_OracleDataReader(dr);
        }

        public List<IncidentAndChangeModel> GetChangeImpactSortCountBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) alls,to_char(TicketCreateTime, 'yyyyMM') sign from change where status > 0 and (Impact = 1000 OR Impact = 2000)";
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            sqlStr += " group by  to_char(TicketCreateTime, 'yyyyMM')";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentAndChangeModel>.GetModel_By_OracleDataReader(dr);
        }

        //服务统计 已解决变更， 所有变更
        public List<SiteBranchAndWorkListModel> GetChangeInfoList(int? site, int? bnid, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select t1.alias alls,t1.count1 sign,t2.count2 sign2 from (select Count(*) count1,s.alias from change i inner join sitebranch s on i.siteid = s.id where 1=1";
            if (site != null)
            {
                sqlStr += " and i.SiteId = :siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and i.BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and i.TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and i.TicketResolvedTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by s.alias ) t1 left join (select Count(*) count2,s2.alias from change i2 inner join sitebranch s2 on i2.siteid = s2.id where i2.status >=10";
            if (site != null)
            {
                sqlStr += " and i2.SiteId = :siteid2";
                cmdParms.Add(new OracleParameter("siteid2", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and i2.BusinessSystemId = :bnid2";
                cmdParms.Add(new OracleParameter("bnid2", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and i2.TicketCreateTime >= :sTime2";
                cmdParms.Add(new OracleParameter("sTime2", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and i2.TicketResolvedTime <= :eTime2";
                cmdParms.Add(new OracleParameter("eTime2", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by s2.alias) t2 on t1.alias =t2.alias";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<SiteBranchAndWorkListModel>.GetModel_By_OracleDataReader(dr);
        }

        public List<SiteBranchAndWorkListModel> GetChangeInfoListBySystemName(int? site, string systemname, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select t1.alias alls,sb.id siteid,t1.count1 sign,t2.count2 sign2 from (select Count(*) count1,s.alias from change i inner join sitebranch s on i.siteid = s.id where 1=1";
            if (site != null)
            {
                sqlStr += " and i.SiteId = :siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(systemname))
            {
                sqlStr += " and i.BusinessSystemId in (select id from BusinessSystem  where name = :systemname)";
                cmdParms.Add(new OracleParameter("systemname", OracleDbType.NVarchar2, systemname, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and i.TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and i.TicketResolvedTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by s.alias ) t1 left join (select Count(*) count2,s2.alias from change i2 inner join sitebranch s2 on i2.siteid = s2.id where i2.status >=10";
            if (site != null)
            {
                sqlStr += " and i2.SiteId = :siteid2";
                cmdParms.Add(new OracleParameter("siteid2", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(systemname))
            {
                sqlStr += " and i2.BusinessSystemId in (select id from BusinessSystem  where name = :systemname2)";
                cmdParms.Add(new OracleParameter("systemname2", OracleDbType.NVarchar2, systemname, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and i2.TicketCreateTime >= :sTime2";
                cmdParms.Add(new OracleParameter("sTime2", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and i2.TicketResolvedTime <= :eTime2";
                cmdParms.Add(new OracleParameter("eTime2", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by s2.alias) t2 on t1.alias =t2.alias left join sitebranch sb on sb.alias=t1.alias";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<SiteBranchAndWorkListModel>.GetModel_By_OracleDataReader(dr);
        }

        public List<IncidentAndChangeModel> GetEscalationChangeCountBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) alls,to_number(changesource) Id from change where 1=1";
            //string sqlStr = "select Count(*)  from escalation e inner join change c on e.destinationid = c.request_id  where e.DestinationType = 2";
            //string sqlStr = "select Count(distinct e.sourceid) from escalation e inner join change c  on e.destinationid = c.request_id inner join change c1 on e.sourceid = c1.request_id where c.siteid = '101' and c1.siteid != '101' and e.DestinationType =2"; 
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            sqlStr += " group by changesource";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentAndChangeModel>.GetModel_By_OracleDataReader(dr);
        }

        public List<IncidentAndChangeModel> GetAvgtimeChangeBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select round(avg(endtime-starttime),4)*24 alls,to_char(TicketCreateTime, 'yyyyMM') sign from change where status >9";
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            sqlStr += " group by  to_char(TicketCreateTime, 'yyyyMM')";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentAndChangeModel>.GetModel_By_OracleDataReader(dr);
        }

        //查询地市未关闭变更(status <  9)
        public int GetUnResolvedChangeCount(int? site, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = string.Empty;
            string whereStr = string.Empty;

            if (site != null)
            {
                whereStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }

            if (sTime != null)
            {
                whereStr += " and TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                whereStr += " and TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }


            sqlStr = "select count(*) from change where status < 7 " + whereStr;


            object obj = oda.ExecuteScalar(sqlStr, cmdParms);
            return Convert.ToInt32(obj);
        }
    }
}
